﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NetMsSqlLibrary
{
    public class SqlManager
    {

        public string LastSql { get; set; }


        /// <summary>
        /// 连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetCon()//连接数据库，ConfigurationManager对象的AppSettings属性值获取配置节中连接数据库的字符串实例化SqlConnection对象，并返回该对象
        {
            return new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString());
        }

        /// <summary>
        /// 查
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="whrere"></param>
        /// <returns></returns>
        public DataTable SelectData(string tableName, WhereList whrereList = null)
        {
            SqlConnection con = GetCon();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            DataTable table ;
            string sqlStr = "select * from " + tableName;
            try
            {
                if (whrereList != null)
                {
                    sqlStr = sqlStr + " where " + whrereList.WhereStr.Substring(4); ;
                }
                cmd.CommandText = sqlStr;
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                table = Tools.ConvertDataReaderToDataTable(dr, tableName);
            }
            catch (Exception ex)
            {
                table = null;
                throw ex;
            }
            finally
            {
                con.Close();
                LastSql = sqlStr;
            }
            return table;
        }

        /// <summary>
        /// 增
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public int InsertData(SqlCommand cmd, string tableName, DataTable table)
        {
            try
            {
                // 验证Datatable
                if (table.Rows.Count <= 0 || table == null)
                {
                    throw new Exception("Datatable不能为空！");
                }
                string keys = null;
                string values = null;
                //遍历字段
                for (int Column = 0; Column < table.Columns.Count; Column++)
                {
                    keys = keys + table.Columns[Column].ColumnName;
                    if (Column < table.Columns.Count - 1)
                    {
                        keys = keys + ",";
                    }
                }
                //遍历值
                int forint = 0;
                foreach (DataRow dr in table.Rows)
                {
                    values = values + "(";
                    for (int value = 0; value < dr.ItemArray.Length; value++)
                    {
                        if (dr[value]!=null)
                        {
                            values = values + "'" + dr[value].ToString() + "'";
                        }
                        else
                        {
                            values = values + "null";
                        }
                        if (value < dr.ItemArray.Length -1)
                        {
                            values = values + ",";
                        }
                    }
                    values = values + ")";
                    forint += 1;
                    if (table.Rows.Count > 1 && forint < table.Rows.Count)
                    {
                        values = values + ",";
                    }
                }
                string sqlStr = "INSERT INTO " + tableName + " (" + keys + ") Values " + values;
                cmd.CommandText = sqlStr;
                int number = cmd.ExecuteNonQuery();
                LastSql = sqlStr;
                return number;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        /// <summary>
        /// 改
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="cmd"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        public int UpData(SqlCommand cmd, string tableName,  DataRow row, WhereList whrereList)
        {
            string sqlStr = "UPDATE " + tableName + " SET ";
            try
            {
                // 验证
                if (row.Table.Columns.Count <= 0 || row == null)
                {
                    throw new Exception("要修改的字段不能为空！");
                }
                //遍历
                foreach (DataColumn key in row.Table.Columns)
                {
                    if (string.IsNullOrEmpty(row[key.ColumnName].ToString())==false)
                    {
                        sqlStr = sqlStr + key.ColumnName + "=" + "'" + row[key.ColumnName] + "',";
                    }
                }
                sqlStr = sqlStr.Substring(0,sqlStr.Length-1);
                sqlStr = sqlStr + " where " + whrereList.WhereStr.Substring(4);
                cmd.CommandText = sqlStr;
                int number = cmd.ExecuteNonQuery();
                LastSql = sqlStr;
                return number;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 删
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="tableName"></param>
        /// <param name="whrereList"></param>
        /// <returns></returns>
        public int DeleteData(SqlCommand cmd, string tableName, WhereList whrereList)
        {
            string sqlStr = "DELETE FROM " + tableName;
            try
            {
                sqlStr = sqlStr + " where " + whrereList.WhereStr.Substring(4);
                cmd.CommandText = sqlStr;
                int number = cmd.ExecuteNonQuery();
                LastSql = sqlStr;
                return number;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 自定义SQL
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public int ExNonQuery(SqlCommand cmd, string sqlStr)
        {
            try
            {
                cmd.CommandText = sqlStr;
                int number = cmd.ExecuteNonQuery();
                LastSql = sqlStr;
                return number;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            
        }
    }
}
